package org.zfes.snowier.zbss.xzy.store.repository.db.mysql;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.zfes.snowier.core.util.ZObjectUtil;
import org.zfes.snowier.core.util.ZStrUtil;
import org.zfes.snowier.zbss.xzy.store.consts.TableDefineConsts;
import org.zfes.snowier.zbss.xzy.store.meta.TableMetaCache;
import org.zfes.snowier.zbss.xzy.store.repository.api.IDDLService;

import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
//SELECT
//TABLE_SCHEMA AS '库名',
//TABLE_NAME AS '表名',
//COLUMN_NAME AS '列名',
//ORDINAL_POSITION AS '列的排列顺序',
//COLUMN_DEFAULT AS '默认值',
//IS_NULLABLE AS '是否为空',
//DATA_TYPE AS '数据类型',
//CHARACTER_MAXIMUM_LENGTH AS '字符最大长度',
//NUMERIC_PRECISION AS '数值精度(最大位数)',
//NUMERIC_SCALE AS '小数精度',
//COLUMN_TYPE AS 列类型,
//COLUMN_KEY 'KEY',
//EXTRA AS '额外说明',
//COLUMN_COMMENT AS '注释'
//FROM
//information_schema.`COLUMNS`
//WHERE
//TABLE_SCHEMA = 'flux_data_device'
//ORDER BY
//TABLE_NAME,
//ORDINAL_POSITION;
//@Controller
//@RequestMapping("/table")
public class MysqlDDLService implements IDDLService ,CommandLineRunner{
	
	private Logger logger = LoggerFactory.getLogger(MysqlDDLService.class);
	
	@Autowired
	JdbcTemplate jdbcTemplate;
	@Autowired 
	TableMetaCache tableMetaCache;
	
	private String dbName;
	
	public MysqlDDLService(String dbName) {
		super();
		this.dbName = dbName;
	}

	@Override
	public void run(String... args) throws Exception {
		
		Map<String,Set<String>> tableInfo = loadTablesMeta(dbName,"");
		
		tableMetaCache.addTables(tableInfo);
		
	}
	
	@Override
	public Map<String,Set<String>> loadTablesMeta(String dbName,String prefix){
		
		Map<String, Set<String>> tableMetaMap=Maps.newHashMap();
		 try {
			 String sql="SELECT TABLE_NAME as tablename, COLUMN_NAME as columnname FROM information_schema.`COLUMNS` where 1=1 ";
			 if(ZStrUtil.hasText(prefix)) {
				 String str=prefix.replace("_", "\\_");
				 sql+=" and table_name like '"+str+"%' ";
			 }
			 sql+=" and  TABLE_SCHEMA = '"+dbName+"' ";
			 sql+="ORDER BY  TABLE_NAME ";
			 logger.info("sql:{}",sql);
			 List<TableCol> tableNames = jdbcTemplate.query(sql, new RowMapper<TableCol> () {
				 @Override
			        public TableCol mapRow(ResultSet resultSet, int i) throws SQLException {
					 return new TableCol(resultSet.getString("tablename"),resultSet.getString("columnname"));
				 	}
			 });
			 logger.info("tableNames:{}",tableNames);
			 
			tableMetaMap = tableNames.stream()
					 .collect(Collectors.groupingBy(TableCol::getTablename,Collectors.mapping(TableCol::getColumnname, Collectors.toSet())));
			 
			 logger.info("tableMetaMap:{}",tableMetaMap);
			 
	        } catch (Exception e) {
	            e.printStackTrace();
	        }
		return tableMetaMap;
	}
	@Override
	public void refreshTable(String tablename,Set<String> colSet){
		
		boolean isTableExists = tableMetaCache.isTableExists(tablename);
		if(!isTableExists) {
			String sql=tableSql( tablename, colSet);
			jdbcTemplate.execute(sql);
		}else {
			Set<String> cachedColSet = tableMetaCache.getCols( tablename);
			
			Set<String>  colSetClone =colSet.stream().map(v->new String(v)).collect(Collectors.toSet());
			
			colSetClone.removeAll(cachedColSet);
			
			if(ZObjectUtil.isNotEmpty(colSetClone)) {
				
				String sql=addColSql( tablename, colSetClone);
				
				jdbcTemplate.execute(sql);
			}
			
		}
		
		reloadMetaData();
	}
	
	 void reloadMetaData() {
		 	Map<String,Set<String>> tableInfo = loadTablesMeta(dbName,"");
			tableMetaCache.addTables(tableInfo);
	}
	static Set<String> defaultHaveCols=Sets.newHashSet("createtime","receivetime","message","id");
	
	private String tableSql(String tablename,Set<String> colSet) {
		  StringBuilder sql  = new StringBuilder();
		  sql.append("  CREATE TABLE IF NOT EXISTS `").append(tablename).append("` (");
		  sql.append(" `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',");
		  sql.append(" `createtime`  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,");
		  sql.append(" `receivetime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',");
		 // sql.append("           `appId` bigint(20) NOT NULL COMMENT '所属应用',");
		 // sql.append("           `tenantid` bigint(20) NOT NULL COMMENT '所属租户',");
		  sql.append("           `message` text COMMENT '信息',");
		  
		  
		  for(String col : colSet){
			  if(defaultHaveCols.contains(col)) {
				  continue;
			  }
			  Integer spLen=TableDefineConsts.sp_col_len.get(col);
			  if(spLen==null) {
				  spLen=30;
			  }
			    sql.append("`").append(col).append("` varchar("+spLen+") DEFAULT '',");
			}
		  sql.append("  PRIMARY KEY (`id`) USING BTREE ");
		  sql.append(") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;");
		  return sql.toString();
	  }
	  
	private String addColSql(String tablename,Set<String> cols) {
		  StringBuilder sql  = new StringBuilder();
		  sql.append("    ALTER TABLE ").append("`").append(tablename).append("`");
		  for(String col:cols){
			  sql.append("  ADD COLUMN `").append(col).append("` varchar(40) DEFAULT '',");
			}
		  
		  sql.append("   ALGORITHM=INPLACE, LOCK=NONE");
		  return sql.toString();
	  }


		class TableCol{
			private String tablename;
			private String columnname;
			
			
			public TableCol(String tablename, String columnname) {
				super();
				this.tablename = tablename;
				this.columnname = columnname;
			}
			public String getTablename() {
				return tablename;
			}
			public void setTablename(String tablename) {
				this.tablename = tablename;
			}
			public String getColumnname() {
				return columnname;
			}
			public void setColumnname(String columnname) {
				this.columnname = columnname;
			}
			@Override
			public String toString() {
				return "TableCol [tablename=" + tablename + ", columnname=" + columnname + "]";
			}
		}


		public String getDbName() {
			return dbName;
		}

		public void setDbName(String dbName) {
			this.dbName = dbName;
		}


	
}
